# 作业：
# 1、做一个学生点名系统，系统里支持：存入学生、更改学生、删除学生、点到学生姓名方法
#

# 存入学生（姓名、年龄、家庭地址、手机号）： 存入成功后，提示xxx存入成功
# 更改学生（姓名、年龄、家庭地址、手机号）：可以修改学生内容的任意一个字段
# 删除学生：删除的学生将不在展示
# 展示全部学生：可以看到全部的学生姓名
# 点到学生姓名：随机抽取任意一个存在的学生信息，然后打印：到！。举例： 刘闯到！
#
# 以上内容，需要连接数据库，每次的数据都需要增、删、改、查去做
#
# student表：name、age、address、phone_number
#
# import pymysql
# import random
# class Student_DataBase():
#     def __init__(self,DBdatabase,user,password,database,cursorclass = None):
#         self.DBdatabase = DBdatabase
#         self.user = user
#         self.password = password
#         self.database = database
#         self.cursorclass = cursorclass
#     def connect(self):                                              #todo:游标！！增删改时必须存在开启数据库，创建游标!
#         self.db = pymysql.connect(host = self.DBdatabase,user=self.user,password=self.password,database=self.database,cursorclass=self.cursorclass)
#         self.cursor = self.db.cursor()
#     def close_cursor(self):                                         #todo:关闭游标！关闭数据库！
#         self.cursor.close()
#         self.db.close()
#     def __edit(self,sql):                                           ##todo：私有方法，内部调用
#         self.connect()                                              #TODO：调用方法，开启数据库
#         try:
#             self.cursor.execute(sql)
#             self.db.commit()
#             return "数据库更新成功！"
#         except:
#             return "数据库更新失败!"
#     def select_all(self,sql):                                       #todo: 查询所有信息
#         self.connect()                                              #TODO：调用方法，开启数据库
#         try:
#             self.cursor.execute(sql)                                #todo:执行传入的sql语句！
#             result_information = self.cursor.fetchall()             #todo：返回全部数据
#             self.close_cursor()                                     #todo:关闭游标！
#         except Exception as  e:
#             print("您输入的查询语句有误！")
#             result_information = None
#         return result_information
#     def delete_student_information(self,sql):                       #todo:删除学生信息！！
#         return  self.__edit(sql)
#     def insert_student_information(self,sql):                       #todo:插入学生信息！！
#         return self.__edit(sql)
#     def update_student_information(self,sql):                       #todo：修改学生信息！！
#         return self.__edit(sql)
#     def random_student(self):
#         self.connect()
#         try:
#             self.cursor.execute("select name from student;")
#             result_information = self.cursor.fetchall()
#             self.close_cursor()
#         except Exception as e:
#             print("您输入的查询语句有误！")
#         list_ = []
#         for  i in result_information:
#             list_.append(i["name"])
#         random_name = random.choice(list_)
#         return f"{random_name}到！"
# db1 = Student_DataBase(DBdatabase="123.249.78.43",user="root",password="123456",database="wave",cursorclass=pymysql.cursors.DictCursor)
# student_name = input("请输入姓名：")
# student_age = input("请输入年龄：")
# student_address = input("请输入地址：")
# student_phone = input("请输入电话话：")
# insert_into_student = "insert into student values(' " + student_name + "',"+student_age+",'"+student_address+"',"+student_phone+")"     #todo:插入学生信息
# print(db1.insert_student_information(insert_into_student))
# print(db1.insert_student_information("insert into student values('小明',21,'辽宁省凌海市',15724661557);"))
# delect_student_name = input("请输入删除的姓名：")
# delect_student_information = "delete from student where name ='"+delect_student_name+"';"                                             #todo：删除学生信息
# print(db1.delete_student_information(delect_student_information))
# update_student_name = input("请输入您要修改的姓名：")
# update_student_name_in = input("请输入您要修改后的姓名：")
# update_student_age_in = input("请输入您要修改后的年龄：")
# update_student_address_in = input("请输入您要修改后的地址：")
# update_student_phone_in = input("请输入您要修改后的电话号码：")
# update_student = "update student set name = '"+update_student_name_in+"' ,age = " + update_student_age_in+" ,address = '"+update_student_address_in+"' ,phone = "+update_student_phone_in+" where name = '"+update_student_name+"';"
# print(db1.update_student_information(update_student))                                                                                   #todo：修改学生信息
# print(db1.select_all("select * from student"))                     #todo:查询学生信息
# print(db1.random_student())                                         #todo:随机学生信息到！
# 2、点餐管理系统
# 1》、餐馆可以新增菜品、下架菜品、修改菜品
# 2》、每个菜品，都有对应的库存，所有菜品的库存均为999，每出一道菜，库存-1
# 菜品有对应的价格，餐厅老板定义价格每道菜不允许超过15元，没有小数单位，均为整数

# 3》、餐馆后台有点赞系统，能够收集客户对菜品的喜好，0~5分
# 4》、有菜品评分提报功能，对平均分数（菜品总分/打分人数）低于3分的菜品进行强制下架；
# 客户端
# perper = 0 类属性
# 总分= 0
# 1》、客户可以进行点餐 调用父类
# 2》、每个客户的起始钱包均为200，订餐后扣减对应金额
# 3》、可以为每个菜进行打分
# import pymysql
#
#
# class DataBase():
#     def __init__(self,host,user,password,database,cursorclass = None):
#         self.host = host
#         self.user = user
#         self.password = password
#         self.database = database
#         self.cursorclass = cursorclass
#         self.money = 200
#     def connect(self):
#         self.db = pymysql.Connect(host=self.host,user=self.user,password=self.password,database=self.database,cursorclass=self.cursorclass)
#         self.cursor = self.db.cursor()
#     def close(self):
#         self.cursor.close()
#         self.db.close()
#     def selec_all(self,sql):        #todo:查看所有
#         self.connect()
#         try:
#             self.cursor.execute(sql)
#             re_information =self.cursor.fetchall()
#             self.close()
#         except :
#             print("输入的select语句有错误！")
#             re_information =None
#         return re_information
#     def __edit(self,sql):
#         self.connect()
#         try:
#             self.cursor.execute(sql)
#             self.db.commit()
#             result = "数据库更新成功！"
#         except:
#             result = "数据库更新失败！"
#         return result
#     def insert(self,sql):         #todo：插入
#         return self.__edit(sql)
#     def delete(self,sql):       #todo:删除
#         return self.__edit(sql)
#     def update(self,sql):     #todo：改
#         return self.__edit(sql)
#     def order_dish(self):               #todo:点菜
#         menu_list =[]
#         menu=self.selec_all("select * from dish;")
#         print(*menu)
#         menu_name = self.selec_all("select dish_name from dish;")
#
#         for i in menu_name:
#             menu_list.append(i["dish_name"])
#         order_dish_name = input("请输入要点的菜名：")
#         dish_="select dish_inventory from dish where dish_name = '"+order_dish_name+"';"
#         dish_price_s = "select dish_price from dish where dish_name = '"+order_dish_name+"';"
#         dish_inventory = self.selec_all(dish_)
#         dish_inventory_ = self.selec_all(dish_price_s)
#         # self.money -=int(dish_price_s)
#         for  i in dish_inventory_:
#             self.money -=i['dish_price']
#         for  i in dish_inventory:
#             dish_inventory_old = i["dish_inventory"]
#         if order_dish_name in menu_list:
#             if dish_inventory_old <0:
#                 print("库存不足")
#             else:
#                 dish_inventory_old -=1
#                 dish_inventory_d = "update dish set dish_inventory ="+str(dish_inventory_old)+" where dish_name ='"+order_dish_name+"';"
#                 self.update(dish_inventory_d)
#                 print("您已点单完毕")
#         else:
#             print("没有这个菜")
#         return f"点的菜是：{order_dish_name},剩{self.money}元"
#     def delete_dish_hobby_dish_avg(self):                 #todo：删除菜品评分小于3的
#         self.delete("delete from dish where hobby_dish_avg < 3;")
# class person(DataBase):
#     def __init__(self,name,host,user,password,database,cursorclass = None):
#         self.name = name
#         self.host = host
#         self.user = user
#         self.password = password
#         self.database = database
#         self.cursorclass = cursorclass
#         self.money = 200
#     def dish_score(self):                                   #todo：菜品评分
#         print(self.selec_all("select * from dish;"))
#         dish_name = input("请输入您要评分的菜品!")
#         count_number = self.selec_all("select count_number from dish where dish_name = '"+dish_name+"'")
#         # self.update("update dish ")
#         for i in count_number:
#             new_count_number = i['count_number']
#         print(new_count_number)
#         hobby_dish_avg = self.selec_all("select hobby_dish_avg from dish where dish_name = '"+dish_name+"'")
#         for i in hobby_dish_avg:
#             old_hobby_dish_avg = i['hobby_dish_avg']
#         print(old_hobby_dish_avg)
#         new_hobby_dish_avg_in = int(input("请输入您要给这道菜打的评分：(0-5分)"))
#         if new_hobby_dish_avg_in<0 or new_hobby_dish_avg_in>5:
#             print("您输入的评分不在评分表中，请输入0-5分")
#         else:
#             new_count_number += 1
#             new_hobby_dish_avg = (old_hobby_dish_avg*(new_count_number-1) + new_hobby_dish_avg_in) / new_count_number
#             print(new_hobby_dish_avg)
#             self.update("update dish set hobby_dish_avg="+str(new_hobby_dish_avg)+",count_number ="+str(new_count_number)+" where dish_name ='"+dish_name+"'")
#             return "评分成功！"
# db1 = DataBase(host="123.249.78.43",user="root",password="123456",database="wave",cursorclass=pymysql.cursors.DictCursor)

# dish_name = input("请输入菜名：")                                #todo：增加菜品
# dish_inventory = 999
# dish_price = int(input("请输入菜品价格："))
# if dish_price >15:
#     print("价格不允许大于15元！")
# elif dish_price <=15:
#     insert_into_dish = "insert into dish(dish_name,dish_inventory,dish_price) values(' " + dish_name + "',"+str(dish_inventory)+","+str(dish_price)+")"
# print(db1.insert(insert_into_dish))
# delect_dish_name = input("请输入删除的菜品名：")                #todo:删除菜品
# delect_student_information = "delete from dish where dish_name ='"+delect_dish_name+"';"
# print(db1.delete(delect_student_information))
# update_dish_name = input("请输入您要修改的菜名：")                #todo：修改菜品
# update_dish_name_in = input("请输入您要修改后的菜名：")
# update_dish_inventory_in = input("请输入您要修改后的库存：")
# update_dish_price_in = input("请输入您要修改后的价格：")
# update_dish = "update dish set dish_name = '"+update_dish_name_in+"' ,dish_inventory = " + update_dish_inventory_in+" ,dish_price = "+update_dish_price_in+" where dish_name = '"+update_dish_name+"';"
# print(db1.update(update_dish))
# print(db1.selec_all("select * from dish;"))
# tabale_id = input("请输入桌号：")
# p1 = person(tabale_id,host="123.249.78.43",user="root",password="123456",database="wave",cursorclass=pymysql.cursors.DictCursor)
# # print(p1.order_dish())                                   #todo:点菜
# print(p1.dish_score())                                       #todo:菜品评分
# print(db1.delete_dish_hobby_dish_avg())                      #todo：删除分数小于3的